﻿Public Class UsrCtrlKetQua
    Public Sub LoadLuaChon()
        cbxLuaChon.Items.Add("--Tìm Theo Học Kỳ--")
        cbxLuaChon.Items.Add("--Tìm Theo Môn Học--")
        cbxLuaChon.Items.Add("--Tìm Theo Lớp--")
        cbxLuaChon.Items.Add("--Tìm Theo Học Sinh--")
        cbxLuaChon.Items.Add("--Tìm Theo Năm Học--")
    End Sub
    Public Sub LoadHocKy()
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim sql As String = "select distinct(TenHocKy) from HOCKY"
        KetQua_BUS.LoadCBX(cbxHocKy, sql)
        cbxHocKy.DisplayMember = "TenHocKy"
        cbxHocKy.ValueMember = "TenHocKy"
    End Sub
    Public Sub LoadMonHoc()
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim sql As String = "select * from MONHOC"
        KetQua_BUS.LoadCBX(cbxMonHoc, sql)
        cbxMonHoc.DisplayMember = "TenMonHoc"
        cbxMonHoc.ValueMember = "MaMonHoc"
    End Sub
    Public Sub LoadDaTa()
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                            "from HOCKY hk, KETQUA kq, MONHOC mh " &
                            "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy"
        KetQua_BUS.LoadDGV(dgvKetQua, sql)
    End Sub

    Private Sub btnSearchKetQua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchKetQua.Click
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        If (cbxLuaChon.Text = "--Tìm Theo Học Kỳ--") Then
            Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                                "from HOCKY hk, KETQUA kq, MONHOC mh " &
                                "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy and hk.MaHocKy like '%" & txtTuKhoa.Text & "%'"
            KetQua_BUS.LoadDGV(dgvKetQua, sql)
        ElseIf (cbxLuaChon.Text = "--Tìm Theo Môn Học--") Then
            Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                                "from HOCKY hk, KETQUA kq, MONHOC mh " &
                                "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy and mh.TenMonHoc Like '%" & txtTuKhoa.Text & "%'"
            KetQua_BUS.LoadDGV(dgvKetQua, sql)
        ElseIf (cbxLuaChon.Text = "--Tìm Theo Lớp--") Then
            Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                                "from HOCKY hk, KETQUA kq, MONHOC mh " &
                                "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy and kq.MaHocSinh like '" & txtTuKhoa.Text & "%'"
            KetQua_BUS.LoadDGV(dgvKetQua, sql)
        ElseIf (cbxLuaChon.Text = "--Tìm Theo Học Sinh--") Then
            Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                                "from HOCKY hk, KETQUA kq, MONHOC mh, HOCSINH hs " &
                                "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy and hs.MaHocSinh = kq.MaHocSinh " &
                                "and kq.MaHocSinh Like '%" & txtTuKhoa.Text & "%'"
            KetQua_BUS.LoadDGV(dgvKetQua, sql)
        ElseIf (cbxLuaChon.Text = "--Tìm Theo Năm Học--") Then
            Dim sql As String = "select kq.MaHocSinh, mh.TenMonHoc, hk.TenHocKy, kq.Diem15Phut, kq.Diem45Phut, kq.DiemCuoiKy, kq.TBCuoiKy, hk.NamHoc " &
                                "from HOCKY hk, KETQUA kq, MONHOC mh, HOCSINH hs " &
                                "where kq.MaMonHoc = mh.MaMonHoc and kq.MaHocKy = hk.MaHocKy and hs.MaHocSinh = kq.MaHocSinh " &
                                "and hk.NamHoc =" & txtTuKhoa.Text
            KetQua_BUS.LoadDGV(dgvKetQua, sql)
        End If
    End Sub

    Private Sub btnThemKetQua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThemKetQua.Click
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim kq As KetQuaDTO = New KetQuaDTO
        kq.MaHocSinh = txtMaHocSinh.Text
        kq.MaMonHoc = cbxMonHoc.SelectedValue
        kq.Diem15Phut = Convert.ToDouble(txtDiem15Phut.Text)
        kq.Diem45Phut = Convert.ToDouble(txtDiem45Phut.Text)
        kq.DiemCK = Convert.ToDouble(txtDiemCuoiKy.Text)
        Dim hk As Integer = cbxHocKy.SelectedValue
        Dim nh As Integer = Convert.ToInt32(txtNamHoc.Text)
        Dim sql As String = "select MaHocKy from HOCKY where TenHocKy = " & hk & " and NamHoc = " & nh
        Dim GetDT As DataProvider = New DataProvider
        Dim reader As SqlClient.SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If reader.Read() Then
            kq.MaHocKy = reader.GetString("0")
        End If
        kq.TBCK = (kq.Diem15Phut + kq.Diem45Phut * 2 + kq.DiemCK * 3) / 6
        If (KetQua_BUS.ThemKetQua(kq)) Then
            MessageBox.Show("Thêm kết quả thành công", "^^", MessageBoxButtons.OK)
            LoadDaTa()
        Else
            MessageBox.Show("Thêm kết quả thất bại, vui lòng kiểm tra lại dữ liệu", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub btnXoaKetQua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoaKetQua.Click
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim index As DataGridViewSelectedRowCollection = dgvKetQua.SelectedRows
        For Each a As DataGridViewRow In index
            Dim MaHocSinh As String = a.Cells(0).Value
            Dim TenMonHoc As String = a.Cells(1).Value
            Dim TenHocKy As String = a.Cells(2).Value
            Dim NamHoc As String = a.Cells(6).Value
            Dim sql As String = "select MaMonHoc from MONHOC where TenMonHoc = N'" & TenMonHoc & "'"
            Dim GetDT As DataProvider = New DataProvider
            Dim reader As SqlClient.SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
            Dim MaMonHoc As String
            If (reader.Read()) Then
                MaMonHoc = reader.GetString("0")
            End If
            sql = "Select MaHocKy from HOCKY where TenHocKy = " & TenHocKy & " and NamHoc = " & NamHoc
            reader = GetDT.SQL_ExcuteQueryReader(sql)
            Dim MaHocKy As String
            If reader.Read() Then
                MaHocKy = reader.GetString("0")
            End If
            If (KetQua_BUS.XoaKetQua(MaHocSinh, MaMonHoc, MaHocKy) = False) Then
                MessageBox.Show("Xóa thất bại, vui lòng kiểm tra lại kết nối", "Opps!", MessageBoxButtons.OK)
                Return
            Else
                'Dim Dtb_BUS As DiemTrungBinhBUS = New DiemTrungBinhBUS
                'Dtb_BUS.XoaDTB(MaHocSinh)
            End If
        Next
        MessageBox.Show("Xóa thành công", "^^", MessageBoxButtons.OK)
        LoadDaTa()
    End Sub

    Private Sub btnSuaKetQua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSuaKetQua.Click
        Dim KetQua_BUS As KetQuaBUS = New KetQuaBUS
        Dim kq As KetQuaDTO = New KetQuaDTO
        kq.MaHocSinh = txtMaHocSinh.Text
        kq.MaMonHoc = cbxMonHoc.SelectedValue
        kq.Diem15Phut = Convert.ToDouble(txtDiem15Phut.Text)
        kq.Diem45Phut = Convert.ToDouble(txtDiem45Phut.Text)
        kq.DiemCK = Convert.ToDouble(txtDiemCuoiKy.Text)
        Dim hk As Integer = cbxHocKy.SelectedValue
        Dim nh As Integer = Convert.ToInt32(txtNamHoc.Text)
        Dim sql As String = "select MaHocKy from HOCKY where TenHocKy = " & hk & " and NamHoc = " & nh
        Dim GetDT As DataProvider = New DataProvider
        Dim reader As SqlClient.SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If reader.Read() Then
            kq.MaHocKy = reader.GetString("0")
        End If
        kq.TBCK = (kq.Diem15Phut + kq.Diem45Phut * 2 + kq.DiemCK * 3) / 6
        If (KetQua_BUS.SuaKetQua(kq)) Then
            MessageBox.Show("Cập nhật kết quả thành công", "^^", MessageBoxButtons.OK)
            LoadDaTa()
        Else
            MessageBox.Show("Cập nhật kết quả thất bại, vui lòng kiểm tra lại dữ liệu", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub dgvKetQua_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvKetQua.SelectionChanged
        txtMaHocSinh.Text = dgvKetQua.CurrentRow.Cells(0).Value
        cbxMonHoc.Text = dgvKetQua.CurrentRow.Cells(1).Value
        cbxHocKy.Text = dgvKetQua.CurrentRow.Cells(2).Value
        txtDiem15Phut.Text = Convert.ToDouble(dgvKetQua.CurrentRow.Cells(3).Value)
        txtDiem45Phut.Text = Convert.ToDouble(dgvKetQua.CurrentRow.Cells(4).Value)
        txtDiemCuoiKy.Text = Convert.ToDouble(dgvKetQua.CurrentRow.Cells(5).Value)
        txtNamHoc.Text = dgvKetQua.CurrentRow.Cells(7).Value
        txtTB.Text = Convert.ToDouble(dgvKetQua.CurrentRow.Cells(6).Value)
    End Sub

End Class
